﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;

namespace DAO
{
    public class PHIEUMUONSACHDAO
    {

        public PHIEUMUONSACHDTO layphieumuon(string madocgia)
        {
            SqlConnection conn = DATAPROVIDER.ConnectSQL();
            string sql = "SELECT * FROM PHIEU_MUON_SACH WHERE MaDocGia = '" + madocgia + "'";
            SqlDataAdapter sqladapter = new SqlDataAdapter(sql, conn);
            DataTable datatable = new DataTable();
            sqladapter.Fill(datatable);
            conn.Dispose();
            sqladapter.Dispose();
            
            DataRow row = datatable.Rows[0];
            PHIEUMUONSACHDTO pmDTO = new PHIEUMUONSACHDTO();
            pmDTO.Maphieumuonsach = Convert.ToInt32(row[0].ToString());
            pmDTO.Madocgia = row[1].ToString();
            pmDTO.Ngaymuon = Convert.ToDateTime(row[2].ToString());
            pmDTO.Ngaytra = Convert.ToDateTime(row[3].ToString());
            return pmDTO;
        }

        public int kiemtradamuonchua(string madocgia)
        {
            SqlConnection conn = DATAPROVIDER.ConnectSQL();
            string sql = "SELECT COUNT(*) FROM PHIEU_MUON_SACH WHERE MaDocGia = '" + madocgia + "'";
            SqlCommand cmd = new SqlCommand(sql, conn);
            object temp = cmd.ExecuteScalar();
            int kq = Convert.ToInt32(temp.ToString());
            conn.Dispose();
            return kq;
        }

        public int lapphieumuon(PHIEUMUONSACHDTO pmsDTO)
        {
            SqlConnection conn = DATAPROVIDER.ConnectSQL();
            string sql = "INSERT INTO PHIEU_MUON_SACH(MaDocGia,NgayMuon,NgayTra) VALUES (@MDG,@NM,@NT)";
            SqlCommand cmd = new SqlCommand(sql, conn);

            SqlParameter para = new SqlParameter("@MDG", System.Data.SqlDbType.NChar, 10);
            para.Value = pmsDTO.Madocgia;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@NM", System.Data.SqlDbType.DateTime);
            para.Value = pmsDTO.Ngaymuon;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@NT", System.Data.SqlDbType.DateTime);
            para.Value = pmsDTO.Ngaytra;
            cmd.Parameters.Add(para);

            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            conn.Dispose();
            if (kq > 0)
            {
                BIENPHIEUMUONSACHDAO dao = new BIENPHIEUMUONSACHDAO();
                int kq1 = dao.capnhatsothutuphieumuon();
            }
            return kq;
        }
    }
}
